package DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import object.ResetBox;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

import POJO.BoxOrderList;
import POJO.Goods;

@Repository
public class GoodsDao {
	@Autowired
	JdbcTemplate jdbcTemplate;

	/**
	 * 保存物品记录
	 * 
	 * @param goods
	 */
	public void save(Goods goods) {
		String saveSql = "INSERT INTO `goods` (" + "`goods_terminalID`, "
				+ "`goods_boxNum`, " + "`goods_orderNo`, "
				+ "`goods_bookingTime`, " + "`goods_deadTime`, " + "`goods_userMobileNum`, "
				+ "`goods_mobileNum`, " + "`goods_openCode`, " + "`goods_isForcedOpen`, "
				+ "`goods_agent_id`, " + "`goods_remark`, " + "`goods_status`) "
				+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?);";
		Object[] params = new Object[] { goods.getTerminalId(),
				goods.getBoxNum(), goods.getOrderNo(), goods.getBookingTime(),goods.getDeadTime(),
				goods.getUserMobileNum(), goods.getCourierMobileNum(),
				goods.getOpenCode(),goods.getIsForcedOpen(), goods.getAgentId(), goods.getRemark(),goods.getStatus() };
		jdbcTemplate.update(saveSql, params);
	}

	/**
	 * 判断openCode是否存在
	 * 
	 * @return
	 */
	public boolean isExistOpenCode(String openCode) {
		String isExistOpenCodeSql = "SELECT count(goods_id) FROM goods where goods_openCode=? and goods_status='0' ";
		Object[] params = new Object[] { openCode };
		int result = jdbcTemplate.queryForObject(isExistOpenCodeSql, params,
				Integer.class);
		if (result == 0) {
			return false;
		} else {
			return true;
		}
	}

	/**
	 * 判断openCode是否存在
	 * 
	 * @return
	 */
	public boolean isExistGetCode(String getCode) {
		String isExistOpenCodeSql = "SELECT count(goods_id) FROM goods where goods_getCode=? and goods_status='3' ";
		Object[] params = new Object[] { getCode };
		int result = jdbcTemplate.queryForObject(isExistOpenCodeSql, params,
				Integer.class);
		if (result == 0) {
			return false;
		} else {
			return true;
		}
	}

	/**
	 * 根据订单号获取物品状态
	 * 
	 * @param orderNo
	 * @return
	 */
	public int getGoodsStatusByOrderNo(String orderNo) {
		int status;
		String getGoodsStatusByOrderNoSql = "SELECT goods_status FROM goods where goods_orderNo=?";
		Object[] params = new Object[] { orderNo };
		try {
			status = jdbcTemplate.queryForObject(getGoodsStatusByOrderNoSql,
					params, Integer.class);
		} catch (Exception e) {
			status = -1;
		}
		return status;
	}
	/**
	 * 根据物品ID获取单号
	 * @param goodsID
	 * @return
	 */
	public String getOrderNoByGoodsID(int goodsID) {
		String orderNum;
		String sql = "SELECT goods_orderNo FROM goods where goods_id=?";
		Object[] params = new Object[] { goodsID };
		try {
			orderNum = jdbcTemplate.queryForObject(sql,
					params, String.class);
		} catch (Exception e) {
			orderNum = null;
		}
		return orderNum;
	}

	/**
	 * 根据单号返回物品信息
	 * 
	 * @param orderNo
	 * @return
	 */
	public Goods getGoodsByOrderNo(String orderNo) {
		String sql = "SELECT * FROM goods where goods_orderNo=?";
		final Goods goods = new Goods();
		Object[] params = new Object[] {orderNo};
		try {
			jdbcTemplate.query(sql, params,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs)
								throws SQLException {
							goods.setId(rs.getInt("goods_id"));
							goods.setUserMobileNum(rs
									.getString("goods_userMobileNum"));
							goods.setTerminalId(rs.getInt("goods_terminalID"));
							goods.setBoxNum(rs.getInt("goods_boxNum"));
							goods.setOrderNo(rs.getString("goods_orderNo"));
							goods.setAgentId(rs.getInt("goods_agent_id"));
							goods.setGetCode(rs.getString("goods_getCode"));
							goods.setOpenCode(rs.getString("goods_openCode"));
							goods.setStatus(rs.getInt("goods_status"));
							goods.setRemark(rs.getString("goods_remark"));
							goods.setBookingTime(rs.getTimestamp("goods_bookingTime"));
							goods.setDeadTime(rs.getTimestamp("goods_deadTime"));
							goods.setIsForcedOpen(rs.getString("goods_isForcedOpen"));
						}
					});
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		if (goods.getId() == -1) {
			return null;
		}
		return goods;
	}

	/**
	 * 根据设备ID和状态返回物品列表
	 * 
	 * @param terminalID
	 * @param status
	 * @return
	 */
	public ArrayList<BoxOrderList> getByTerminalIDAndRemark(int terminalID,
			String remark) {
		final ArrayList<BoxOrderList> boxOrderLists = new ArrayList<BoxOrderList>();
		String sql = "SELECT goods_orderNo,goods_mobileNum,goods_boxNum,goods_openCode FROM goods where goods_terminalID=? and goods_remark=?";
		Object[] params = new Object[] {terminalID,remark};
		try {
			jdbcTemplate.query(sql, params, new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					BoxOrderList boxOrderList = new BoxOrderList();
					boxOrderList.setBoxNum(String.valueOf(rs.getInt("goods_boxNum")));
					boxOrderList.setMobileNum(rs.getString("goods_mobileNum"));
					boxOrderList.setOpenCode(rs.getString("goods_openCode"));
					boxOrderList.setOrderNum(rs.getString("goods_orderNo"));
					boxOrderLists.add(boxOrderList);
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		if (boxOrderLists.isEmpty()) {
			return null;
		} else {
			return boxOrderLists;
		}
	}
	
	public ArrayList<ResetBox> getResetBox(int terminalID,
			String remark) {
		final ArrayList<ResetBox> resetBoxs = new ArrayList<ResetBox>();
		String sql = "SELECT goods_orderNo,goods_mobileNum,goods_boxNum,goods_getCode FROM goods where goods_terminalID=? and goods_remark=?";
		Object[] params = new Object[] {terminalID,remark};
		try {
			jdbcTemplate.query(sql, params, new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					ResetBox resetBox = new ResetBox();
					resetBox.setBoxSeq(String.valueOf(rs.getInt("goods_boxNum")));
					resetBox.setItemNum(rs.getString("goods_orderNo"));
					resetBox.setMobileNum(rs.getString("goods_mobileNum"));
					resetBox.setOpenCode(rs.getString("goods_getCode"));
					resetBox.setType("0");
					resetBoxs.add(resetBox);
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		if (resetBoxs.isEmpty()) {
			return null;
		} else {
			return resetBoxs;
		}
	}

	// public int getGoodsStatusByTerminalIDAndBoxSeq(int terminalId,int
	// boxSeq){
	// int status;
	// String
	// sql="SELECT goods_status FROM goods where where goods_terminalID=? and goods_boxNum=?";
	// Object[] params=new Object[]{terminalId,boxSeq};
	// try {
	// status=jdbcTemplate.queryForObject(sql, params,Integer.class);
	// } catch (Exception e) {
	// status=-1;
	// }
	// return status;
	// }

	/**
	 * 根据设备和开箱码确定该打开哪个箱子
	 * 
	 * @param terminalID
	 * @param status
	 * @param openCode
	 * @return
	 */
	public Goods getboxNumByTerminalIDAndStatusAndOpenCode(int terminalID,
			int status, String openCode) {
		String sql = "SELECT * FROM goods "
				+ " where goods_terminalID=? and goods_status=? and goods_openCode=?;";
		final Goods goods = new Goods();
		Object[] params = new Object[] { terminalID, status, openCode };
		try {
			jdbcTemplate.query(sql, params, new RowCallbackHandler() {

				public void processRow(ResultSet rs) throws SQLException {
					goods.setId(rs.getInt("goods_id"));
					goods.setUserMobileNum(rs.getString("goods_userMobileNum"));
					goods.setTerminalId(rs.getInt("goods_terminalID"));
					goods.setBoxNum(rs.getInt("goods_boxNum"));
					goods.setOrderNo(rs.getString("goods_orderNo"));
					goods.setAgentId(rs.getInt("goods_agent_id"));
					goods.setGetCode(rs.getString("goods_getCode"));
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		if (goods.getId() == -1) {
			return null;
		}
		return goods;
	}

	public Goods getboxNumByTerminalIDAndStatusAndGetCode(int terminalID,
			int status, String getCode) {
		String sql = "SELECT * FROM goods "
				+ " where goods_terminalID=? and goods_status=? and goods_getCode=?;";
		final Goods goods = new Goods();
		Object[] params = new Object[] { terminalID, status, getCode };
		try {
			jdbcTemplate.query(sql, params, new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					goods.setId(rs.getInt("goods_id"));
					goods.setUserMobileNum(rs.getString("goods_userMobileNum"));
					goods.setTerminalId(rs.getInt("goods_terminalID"));
					goods.setBoxNum(rs.getInt("goods_boxNum"));
					goods.setOrderNo(rs.getString("goods_orderNo"));
					goods.setAgentId(rs.getInt("goods_agent_id"));
					goods.setGetCode(rs.getString("goods_getCode"));
				}
			});
		} catch (Exception e) {
			return null;
		}
		if (goods.getId() == -1) {
			return null;
		}
		return goods;
	}

	/**
	 * 更新物品状态以及取物码
	 * 
	 * @param storeTime
	 * @param terminalId
	 * @param boxSeq
	 * @param status
	 * @param openCode
	 * @param getCode
	 */
	public void updateStatusAndGetCodeByterminalIDAndBoxSeq(
			Timestamp storeTime, int terminalId, int boxSeq, int status,
			String openCode, String getCode) {
		String sql = "UPDATE `goods` SET `goods_storeTime`=?,`goods_status`=?,`goods_getCode`=? "
				+ " WHERE goods_terminalID=? and goods_boxNum=? and goods_openCode=?;";
		Object[] params = new Object[] { storeTime, status, getCode,
				terminalId, boxSeq, openCode };
		jdbcTemplate.update(sql, params);
	}

	/**
	 * 更新状态和取物时间
	 * 
	 * @param terminalId
	 * @param getTime
	 * @param boxSeq
	 * @param status
	 * @param getCode
	 */
	public void updateStatusAndGetTimeByterminalIDAndBoxSeq(int terminalId,
			Timestamp getTime, int boxSeq, int status, String getCode) {
		String sql = "UPDATE `goods` SET `goods_status`=?,`goods_getTime`=? "
				+ " WHERE goods_terminalID=? and goods_boxNum=? and `goods_getCode`=?;";
		Object[] params = new Object[] { status, getTime, terminalId, boxSeq,
				getCode };
		jdbcTemplate.update(sql, params);
	}

	/**
	 * 根据订单号更新物品状态
	 * 
	 * @param orderNo
	 * @param status
	 * @return
	 */
	public boolean updateStatusByOrderNo(String orderNo, String status,String remark) {
		String sql = "update `goods` set `goods_status`=?,`goods_remark`=? where `goods_orderNo`=?;";
		Object[] params = new Object[] { status, remark,orderNo};
		try {
			jdbcTemplate.update(sql, params);
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	
	public boolean resetByOrderNo(String orderNo, String status,String remark) {
		String sql = "update `goods` set `goods_status`=?,`goods_remark`=?,`goods_getTime`=? where `goods_orderNo`=?;";
		Object[] params = new Object[] { status, remark,null,orderNo};
		try {
			jdbcTemplate.update(sql, params);
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	
	/**
	 * 改变物品标记
	 * @param orderNo
	 * @param remark
	 * @return
	 */
	public boolean updateRemarkByOrderNo(String orderNo, String remark) {
		String sql = "update `goods` set `goods_remark`=? where `goods_orderNo`=?;";
		Object[] params = new Object[] { remark, orderNo };
		try {
			jdbcTemplate.update(sql, params);
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	/**
	 * 修改强制开箱标志
	 * @param orderNo
	 * @param isForcedOpen
	 * @return
	 */
	public boolean updateForcedOpenByOrderNo(String orderNo, String isForcedOpen) {
		String sql = "update `goods` set `goods_isForcedOpen`=? where `goods_orderNo`=?;";
		Object[] params = new Object[] { isForcedOpen, orderNo };
		try {
			jdbcTemplate.update(sql, params);
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	/**
	 * 根据是否强制开箱更新物品状态
	 * @param orderNo
	 * @param status
	 * @param isForcedOpen
	 * @return
	 */
	public boolean updateStatusByIsForceOpen(String orderNo, String status,String isForcedOpen) {
		String sql = "update `goods` set `goods_status`=?,`goods_isForcedOpen`=? where `goods_orderNo`=?;";
		Object[] params = new Object[] { status,isForcedOpen, orderNo };
		try {
			jdbcTemplate.update(sql, params);
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	/**
	 * 获取用户手机号码
	 * 
	 * @param terminalId
	 * @param boxSeq
	 * @param status
	 * @return
	 */
	public String getUserMobile(int terminalId, int boxSeq, int status) {
		String sql = "SELECT goods_userMobileNum FROM goods "
				+ "where goods_terminalID=? and goods_boxNum=? and goods_status=?;";
		Object[] params = new Object[] { terminalId, boxSeq, status };
		String userMobileNum = null;
		try {
			userMobileNum = jdbcTemplate.queryForObject(sql, params,
					String.class);
		} catch (Exception e) {
		}
		return userMobileNum;
	}
	
	/**
	 * 获取超时物品列表
	 * @param terminalID
	 * @param status
	 * @return
	 */
	public ArrayList<Goods> getTimeOutGoods(int terminalID,String status) {
		final ArrayList<Goods> goods = new ArrayList<Goods>();
		String sql = "SELECT goods_orderNo,goods_boxNum,goods_agent_id,goods_openCode,goods_mobileNum FROM goods where goods_terminalID=? and goods_status=? and goods_deadTime<=? and goods_remark=1";
		Timestamp timestamp = new Timestamp(new Date().getTime());
		Object[] params = new Object[] {terminalID, status,timestamp};
		try {
			jdbcTemplate.query(sql, params, new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					Goods good = new Goods();
					good.setOrderNo(rs.getString("goods_orderNo"));
					good.setAgentId(rs.getInt("goods_agent_id"));
					good.setBoxNum(rs.getInt("goods_boxNum"));
					good.setOpenCode(rs.getString("goods_openCode"));
					good.setCourierMobileNum(rs.getString("goods_mobileNum"));
					goods.add(good);
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		if (goods.isEmpty()) {
			return null;
		} else {
			return goods;
		}
	}

}
